package banco.atualizacaoImpostos;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import utilidades.FormatarData;
import dominio.*;

import banco.Banco;
import dominio.atualizacaoImpostos.Atualizacao;
import dominio.proadi.Proadi;
import fachada.atualizacaoImpostos.Fachada_Atualizacao;

public class AtualizacaoDAO implements Fachada_Atualizacao {

	/* (non-Javadoc)
	 * @see banco.atualizacaoImpostos.Fachada_Atualizacao#insertAtualizacao(dominio.atualizacaoImpostos.Atualizacao, dominio.Cliente)
	 */
	@SuppressWarnings("finally")
	public void insertDetalhe_Atualizacao(Atualizacao at, Cliente c, int id) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("insert into detalhe_atualizacao_impostos values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?" +
	            		", ?, ?, ?, ?, ?, ?, ?)");
	            stmt.setString(1, FormatarData.formatar(at.getData()));
	            stmt.setFloat(2, at.getValor_principal());
	            stmt.setFloat(3, at.getTaxa_selic());
	            stmt.setFloat(4, at.getJuros_selic());
	            stmt.setFloat(5, at.getJuros_selic_1_porc());
	            stmt.setFloat(6, at.getSaldo_antes_compensar());
	            stmt.setFloat(7, at.getValor_compensado());
	            stmt.setFloat(8, at.getSaldo_apos_compensar());
	            stmt.setFloat(9, at.getSac_com_juros_selic());
	            stmt.setFloat(10, at.getSaldo_sem_juros());
	            stmt.setFloat(11, at.getSaldo_contabil());
	            stmt.setFloat(12, at.getPorc_ssj_sa());
	            stmt.setFloat(13, at.getParc_comp_pr());
	            stmt.setFloat(14, at.getParc_comp_jrs());
	            stmt.setString(15, at.getImposto().trim());
	            stmt.setString(16, c.getCnpj().trim());
	            stmt.setInt(17, id);
	            stmt.executeUpdate();
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        
	        catch (Exception e) {
	        	e.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);		   
	        }
	}
	
	@SuppressWarnings("finally")
	public void insertAtualizacao(Atualizacao at, Cliente c) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("insert into atualizacao_impostos values (?, ?, ?, ?)");
	            stmt.setString(1, at.getImposto().trim());
	            stmt.setString(2, c.getCnpj().trim());
	            stmt.setString(3, FormatarData.formatar(at.getData()));
	            stmt.setFloat(4, at.getValor_principal());
	            stmt.executeUpdate();
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        
	        catch (Exception e) {
	        	e.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);		   
	        }
	}
	
	@SuppressWarnings("finally")
	public Collection selectAtualizacao_Cliente(Cliente c) throws IOException {
		
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Atualizacao at;
	    Collection<Atualizacao> res = new ArrayList<Atualizacao>();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from atualizacao_impostos where cnpj_cliente = ?");
	        stmt.setString(1, c.getCnpj().trim());
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	at = new Atualizacao();
	        	at.setData(FormatarData.inserirData(rs.getString("data")));
	        	at.setData_string(rs.getString("data"));
	        	at.setImposto(rs.getString("imposto"));
	        	at.setValor_principal(rs.getFloat("valor"));
	        	at.setId(rs.getInt("id"));
	        	res.add(at);
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return res;
	    }
	    
	}
	
	@SuppressWarnings("finally")
	public Atualizacao selectAtualizacao(Atualizacao atu, Cliente c) throws IOException {
		
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Atualizacao at = null;
	    Collection<Atualizacao> res = new ArrayList<Atualizacao>();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from atualizacao_impostos where cnpj_cliente = ? and valor = ? and imposto = ? order by id DESC limit 1");
	        stmt.setString(1, c.getCnpj().trim());
	        stmt.setFloat(2, atu.getValor_principal());
	        stmt.setString(3, atu.getImposto().trim());
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	at = new Atualizacao();
	        	at.setData(FormatarData.inserirData(rs.getString("data")));
	        	at.setData_string(rs.getString("data"));
	        	at.setImposto(rs.getString("imposto"));
	        	at.setValor_principal(rs.getFloat("valor"));
	        	at.setId(rs.getInt("id"));
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return at;
	    }
	    
	}
	
	@SuppressWarnings("finally")
	public Atualizacao selectDetalhes_Atualizacao(Atualizacao atu, Cliente c, int id) throws IOException {
		
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Atualizacao at = new Atualizacao();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from detalhe_atualizacao_impostos where cnpj_cliente = ? and imposto = ? and id_at_imp = ? order by id DESC limit 1");
	        stmt.setString(1, c.getCnpj().trim());
	        stmt.setString(2, atu.getImposto().trim());
	        stmt.setInt(3, id);
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	at.setData(FormatarData.inserirData(rs.getString("data")));
	        	at.setData_string(rs.getString("data"));
	        	at.setImposto(rs.getString("imposto"));
	        	at.setJuros_selic(rs.getFloat("juros_selic"));
	        	at.setJuros_selic_1_porc(rs.getFloat("juros_selic_1_porc"));
	        	at.setParc_comp_jrs(rs.getFloat("parc_comp_jrs"));
	        	at.setParc_comp_pr(rs.getFloat("parc_comp_pr"));
	        	at.setPorc_ssj_sa(rs.getFloat("porc_ssj_sa"));
	        	at.setSac_com_juros_selic(rs.getFloat("sac_com_juros_selic"));
	        	at.setSaldo_apos_compensar(rs.getFloat("saldo_apos_compensar"));
	        	at.setSaldo_antes_compensar(rs.getFloat("saldo_compensado"));
	        	at.setSaldo_contabil(rs.getFloat("saldo_contabil"));
	        	at.setSaldo_sem_juros(rs.getFloat("saldo_sem_juros"));
	        	at.setTaxa_selic(rs.getFloat("taxa_selic"));
	        	at.setValor_compensado(rs.getFloat("valor_compensado"));
	        	at.setValor_principal(rs.getFloat("valor_principal"));
	        	at.setId(rs.getInt("id"));
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return at;
	    }
	    
	}
	
	@SuppressWarnings("finally")
	public Collection selectDetalhes_Atua(Atualizacao atu, Cliente c) throws IOException {
		
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Collection<Atualizacao> res = new ArrayList<Atualizacao>();
	    Atualizacao at = null;
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from detalhe_atualizacao_impostos where cnpj_cliente = ? and imposto = ? and id_at_imp = ?");
	        stmt.setString(1, c.getCnpj().trim());
	        stmt.setString(2, atu.getImposto().trim());
	        stmt.setInt(3, atu.getId());
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	at = new Atualizacao();
	        	at.setData(FormatarData.inserirData(rs.getString("data")));
	        	at.setData_string(rs.getString("data"));
	        	at.setImposto(rs.getString("imposto"));
	        	at.setJuros_selic(rs.getFloat("juros_selic"));
	        	at.setJuros_selic_1_porc(rs.getFloat("juros_selic_1_porc"));
	        	at.setParc_comp_jrs(rs.getFloat("parc_comp_jrs"));
	        	at.setParc_comp_pr(rs.getFloat("parc_comp_pr"));
	        	at.setPorc_ssj_sa(rs.getFloat("porc_ssj_sa"));
	        	at.setSac_com_juros_selic(rs.getFloat("sac_com_juros_selic"));
	        	at.setSaldo_apos_compensar(rs.getFloat("saldo_apos_compensar"));
	        	at.setSaldo_antes_compensar(rs.getFloat("saldo_compensado"));
	        	at.setSaldo_contabil(rs.getFloat("saldo_contabil"));
	        	at.setSaldo_sem_juros(rs.getFloat("saldo_sem_juros"));
	        	at.setTaxa_selic(rs.getFloat("taxa_selic"));
	        	at.setValor_compensado(rs.getFloat("valor_compensado"));
	        	at.setValor_principal(rs.getFloat("valor_principal"));
	        	at.setId(rs.getInt("id"));
	        	res.add(at);
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return res;
	    }
	    
	}
	
}


